﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StoragePeriod.IDAL.RawDict;
using StoragePeriod.Model.RawDict;
using System.Data;
using StoragePeriod.DBUtility;
using System.Data.SqlClient;

namespace StoragePeriod.SqlServerDAL.RawDict
{
    public class D_NutrientComposition : I_NutrientComposition
    {
        /// <summary>
        /// 获取原料
        /// </summary>
        /// <param name="eNutrientComposition">元素详细</param>
        /// <returns>返回原料对应元素列表</returns>
        public List<E_RawElementInfo> GetRawElementList(E_NutrientComposition eNutrientComposition)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select A.RawID,A.NutritionElementsID,B.Elements,A.Content from dbo.NutrientComposition AS A join dbo.NutritionElements AS B ");
            sbSql.Append(" on A.NutritionElementsID=B.id where A.RawID=" + eNutrientComposition.RawID);
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                List<E_RawElementInfo> EntityList = new ModelHandler<E_RawElementInfo>().FillModel(dt);
                return EntityList;
            }
            return null;
        }

        /// <summary>
        /// 根据原料ID 获取对应的元素值
        /// </summary>
        /// <param name="eNutrientCompostion">查询实体</param>
        /// <returns>返回对应的所有元素</returns>
        public List<E_NutrientComposition> GetElementList(E_NutrientComposition eNutrientCompostion)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("select * from dbo.NutrientComposition where RawID=" + eNutrientCompostion.RawID);
            DataTable dt = DbHelperSQL.Query(sbSql.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                List<E_NutrientComposition> EntityList = new ModelHandler<E_NutrientComposition>().FillModel(dt);
                return EntityList;
            }
            return null;
        }

        /// <summary>
        /// 根据原料ID 删除对应的元素
        /// </summary>
        /// <param name="eNutrientCompostion"></param>
        /// <returns>返回受影响行数</returns>
        public bool RemoveByRawID(E_NutrientComposition eNutrientCompostion)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from NutrientComposition ");
            strSql.Append(" where RawID=@RawID");
            SqlParameter[] parameters = {
					new SqlParameter("@RawID", SqlDbType.Int,4)};
            parameters[0].Value = eNutrientCompostion.RawID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 添加元素内容
        /// </summary>
        /// <param name="eNutrientCompostion">要添加的元素值</param>
        /// <returns>返回受影响行数</returns>
        public int Add(E_NutrientComposition eNutrientCompostion)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into NutrientComposition(");
            strSql.Append("RawID,NutritionElementsID,Content,temp1,temp2)");
            strSql.Append(" values (");
            strSql.Append("@RawID,@NutritionElementsID,@Content,@temp1,@temp2)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@RawID", SqlDbType.Int,4),
					new SqlParameter("@NutritionElementsID", SqlDbType.Int,4),
					new SqlParameter("@Content", SqlDbType.VarChar,50),
					new SqlParameter("@temp1", SqlDbType.VarChar,200),
					new SqlParameter("@temp2", SqlDbType.VarChar,200)};
            parameters[0].Value = eNutrientCompostion.RawID;
            parameters[1].Value = eNutrientCompostion.NutritionElementsID;
            parameters[2].Value = eNutrientCompostion.Content;
            parameters[3].Value = eNutrientCompostion.temp1;
            parameters[4].Value = eNutrientCompostion.temp2;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
    }
}
